﻿using System.Data;
using System.Data.SqlClient;
using ProjectProperties;

namespace BusinessLayer
{
    public class CompanySQL
    {
        #region Private Variables
        //private SqlConnection sqlCone;
        private SqlParameter[] arlParms;
        private DataSet dsRetrn;
        #endregion

        public CompanySQL()
        {
            //sqlCone = new SqlConnection("Data Source=OGLESPARK1-PC\\SQLEXPRESS;Initial Catalog=Sample;Integrated Security=true");
            dsRetrn = new DataSet();
        }

        ~CompanySQL()
        {

        }

        public DataSet DisplayTop100Company()
        {
            //SqlCommand oSqlCommand = new SqlCommand();
            //oSqlCommand.Connection = sqlCone;
            //oSqlCommand.CommandText = "Company_DisplayTop100";
            //oSqlCommand.CommandType = CommandType.StoredProcedure;
            //using (SqlDataAdapter daAdapter = new SqlDataAdapter(oSqlCommand))
            //{
            //    dsRetrn = new DataSet();
            //    daAdapter.Fill(dsRetrn);
            //    oSqlCommand.Parameters.Clear();
            //}
            //return dsRetrn;
            return SQLHelper.ExecuteDataset("Company_DisplayTop100");
        }

        public DataSet SelectTop100Company(CompanyInfo oCompanyInfo)
        {
            //SqlCommand oSqlCommand = new SqlCommand();
            //oSqlCommand.Connection = sqlCone;
            //oSqlCommand.CommandText = "Company_SelectTop100";
            //oSqlCommand.CommandType = CommandType.StoredProcedure;
            //oSqlCommand.Parameters.AddWithValue("@Company_Name", oCompanyInfo.Company_Name);
            //using (SqlDataAdapter daAdapter = new SqlDataAdapter(oSqlCommand))
            //{
            //    dsRetrn = new DataSet();
            //    daAdapter.Fill(dsRetrn);
            //    oSqlCommand.Parameters.Clear();
            //}
            //return dsRetrn;
            return SQLHelper.ExecuteDataset("Company_SelectTop100", new SqlParameter("@Company_Name", oCompanyInfo.Company_Name));
        }

        public void InsertCompany(CompanyInfo oCompanyInfo)
        {
            //SqlCommand oSqlCommand = new SqlCommand();
            //oSqlCommand.Connection = sqlCone;
            //oSqlCommand.CommandText = "Company_InsertCompany";
            //oSqlCommand.CommandType = CommandType.StoredProcedure;            
            //oSqlCommand.Parameters.AddWithValue("@Company_Name", oCompanyInfo.Company_Name);
            //sqlCone.Open();
            //oSqlCommand.ExecuteNonQuery();
            //sqlCone.Close();
            SQLHelper.ExecuteNonQuery("Company_InsertCompany", new SqlParameter("@Company_Name", oCompanyInfo.Company_Name));
        }

        public void UpdateCompany(CompanyInfo oCompanyInfo)
        {
            //SqlCommand oSqlCommand = new SqlCommand();
            //oSqlCommand.Connection = sqlCone;
            //oSqlCommand.CommandText = "Company_UpdateCompany";
            //oSqlCommand.CommandType = CommandType.StoredProcedure;
            //oSqlCommand.Parameters.AddWithValue("@Company_Id", oCompanyInfo.Company_Id);
            //oSqlCommand.Parameters.AddWithValue("@Company_Name", oCompanyInfo.Company_Name);
            //sqlCone.Open();
            //oSqlCommand.ExecuteNonQuery();
            //sqlCone.Close();
            //oSqlParameter = new SqlParameter
            arlParms = new SqlParameter[2];
            arlParms[0] = new SqlParameter("@Company_Id", oCompanyInfo.Company_Id);
            arlParms[1] = new SqlParameter("@Company_Name", oCompanyInfo.Company_Name);
            SQLHelper.ExecuteNonQuery("Company_UpdateCompany", arlParms);
        }
    }
}
